﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Skynet.BusinessLogic.DataAccess;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.Unity;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;
using System.Data;  
namespace Skynet.BusinessLogic.DataAccess
{
    public class ExamCategoryDAABRepository : IExamCategoryRepository
    {
        [Dependency("SkynetLibrary")]
        public Database SkynetLibrary { get; set; }
        
        public void Add(BusinessEntities.ExamCategory item)
        {
            DbCommand cmd = null;
            try
            {
                cmd = this.SkynetLibrary.GetStoredProcCommand("usp_ExamCategories_Insert");
                SkynetLibrary.AddInParameter(cmd, "Id", System.Data.DbType.Guid, item.Id);
                SkynetLibrary.AddInParameter(cmd, "Title", System.Data.DbType.String, item.Title);
                SkynetLibrary.AddInParameter(cmd, "Description", System.Data.DbType.String, item.Description);
                this.SkynetLibrary.ExecuteNonQuery(cmd);  
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }        
        }

        public void Add(IEnumerable<BusinessEntities.ExamCategory> items)
        {
            DbConnection con = null;  
            DbCommand cmd = null;
            DbTransaction tran = null;
            try
            {
                con = SkynetLibrary.CreateConnection();

                cmd = this.SkynetLibrary.GetStoredProcCommand("usp_ExamCategories_Insert");
                SkynetLibrary.AddInParameter(cmd, "Id", System.Data.DbType.Guid);
                SkynetLibrary.AddInParameter(cmd, "Title", System.Data.DbType.String);
                SkynetLibrary.AddInParameter(cmd, "Description", System.Data.DbType.String);

                con.Open();
                tran = con.BeginTransaction();

                foreach (var item in items)
                {
                    SkynetLibrary.SetParameterValue(cmd, "Id", item.Id);
                    SkynetLibrary.SetParameterValue(cmd, "Title", item.Title);
                    SkynetLibrary.SetParameterValue(cmd, "Description", item.Description);
                    this.SkynetLibrary.ExecuteNonQuery(cmd, tran);
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                if (tran != null)
                    tran.Rollback();

                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public void Update(BusinessEntities.ExamCategory item)
        {
            DbCommand cmd = null;
            try
            {
                cmd = this.SkynetLibrary.GetStoredProcCommand("usp_ExamCategories_Update");
                SkynetLibrary.AddInParameter(cmd, "Id", System.Data.DbType.Guid, item.Id);
                SkynetLibrary.AddInParameter(cmd, "Title", System.Data.DbType.String, item.Title);
                SkynetLibrary.AddInParameter(cmd, "Description", System.Data.DbType.String, item.Description);
                this.SkynetLibrary.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }        
        }

        public void Update(IEnumerable<BusinessEntities.ExamCategory> items)
        {
            DbConnection con = null;
            DbCommand cmd = null;
            DbTransaction tran = null;
            try
            {
                con = SkynetLibrary.CreateConnection();

                cmd = this.SkynetLibrary.GetStoredProcCommand("usp_ExamCategories_Update");
                SkynetLibrary.AddInParameter(cmd, "Id", System.Data.DbType.Guid);
                SkynetLibrary.AddInParameter(cmd, "Title", System.Data.DbType.String);
                SkynetLibrary.AddInParameter(cmd, "Description", System.Data.DbType.String);

                con.Open();
                tran = con.BeginTransaction();

                foreach (var item in items)
                {
                    SkynetLibrary.SetParameterValue(cmd, "Id", item.Id);
                    SkynetLibrary.SetParameterValue(cmd, "Title", item.Title);
                    SkynetLibrary.SetParameterValue(cmd, "Description", item.Description);
                    this.SkynetLibrary.ExecuteNonQuery(cmd, tran);
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                if (tran != null)
                    tran.Rollback();

                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public void Delete(BusinessEntities.ExamCategory item)
        {
            DbCommand cmd = null;
            try
            {
                cmd = this.SkynetLibrary.GetStoredProcCommand("usp_ExamCategories_Delete");
                SkynetLibrary.AddInParameter(cmd, "Id", System.Data.DbType.Guid, item.Id);                
                this.SkynetLibrary.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }      
        }

        public void Delete(IEnumerable<BusinessEntities.ExamCategory> items)
        {
            DbConnection con = null;
            DbCommand cmd = null;
            DbTransaction tran = null;
            try
            {
                con = SkynetLibrary.CreateConnection();

                cmd = this.SkynetLibrary.GetStoredProcCommand("usp_ExamCategories_Delete");
                SkynetLibrary.AddInParameter(cmd, "Id", System.Data.DbType.Guid);
                
                con.Open();
                tran = con.BeginTransaction();

                foreach (var item in items)
                {
                    SkynetLibrary.SetParameterValue(cmd, "Id", item.Id);                  
                    this.SkynetLibrary.ExecuteNonQuery(cmd, tran);
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                if (tran != null)
                    tran.Rollback();

                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public BusinessEntities.ExamCategory GetOne(object key)
        {
            var mapper = MapBuilder<BusinessEntities.ExamCategory>.MapNoProperties().MapByName(x=>x.Id).MapByName(x=>x.Title ).MapByName(x=>x.Description).Build();
            var result = this.SkynetLibrary.ExecuteSprocAccessor<BusinessEntities.ExamCategory>("usp_ExamCategories_GetOne", mapper,new object[]{key});
            return result.SingleOrDefault();
        }
        public IEnumerable<BusinessEntities.ExamCategory> GetAll()
        {
            var mapper = MapBuilder<BusinessEntities.ExamCategory>.MapAllProperties().Build();
            var result = this.SkynetLibrary.ExecuteSprocAccessor<BusinessEntities.ExamCategory>("usp_ExamCategories_GetAll ", mapper );
            return result;
        }


        public System.Data.IDataReader GetAllStream()
        {
            var cmd = this.SkynetLibrary.GetStoredProcCommand("usp_ExamCategories_GetAll");
            return this.SkynetLibrary.ExecuteReader(cmd);
        }


        public System.Data.DataSet GetTable()
        {
            DbCommand cmd = null;
            DataSet result = null; 
            try
            {
                cmd = this.SkynetLibrary.GetStoredProcCommand("usp_ExamCategories_GetAll");              
                result  = this.SkynetLibrary.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                bool rethrow = ExceptionPolicy.HandleException(ex, Infrastructure.Aspect.ExceptionHandling.ExceptionPolicyNames.DataAccessPolicy);
                if (rethrow)
                    throw;
            }
            return result; 
        }


        DataTable IExamCategoryRepository.GetTable()
        {
            throw new NotImplementedException();
        }

        public void UpdateTable(DataTable ds)
        {
            throw new NotImplementedException();
        }


        public void Maintenance(BusinessEntities.ExamCategory item)
        {
            throw new NotImplementedException();
        }
    }
}
